﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace JsonSubmitForm.App_Code
{
    public class DataObject
    {
        SqlConnection objConn;

        public DataObject()
        {
            objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["connStr"].ToString());
        }

        public void CloseDBConnection()
        {
            objConn.Close();
            objConn.Dispose();
        }

        public SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand objCmd = BuildQueryCommand(storedProcName, parameters);
            objCmd.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int,
                4, /* Size */
                ParameterDirection.ReturnValue,
                false, /* is nullable */
                0, /* byte precision */
                0, /* byte scale */
                string.Empty,
                DataRowVersion.Default,
                null));
            return objCmd;
        }

        private SqlCommand BuildQueryCommand(string storedProcName)
        {
            SqlCommand objCmd = new SqlCommand(storedProcName, objConn);
            objCmd.CommandType = CommandType.StoredProcedure;

            return objCmd;
        }

        public SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand objCmd = new SqlCommand(storedProcName, objConn);
            objCmd.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter parameter in parameters)
            {
                objCmd.Parameters.Add(parameter);
            }
            return objCmd;
        }

        public int InsertData(string storedProcName, IDataParameter[] parameters)
        {
            int intResult = 0;
            if (objConn.State == System.Data.ConnectionState.Closed)
            {
                objConn.Open();
            }

            //objCon.Open();
            SqlCommand objCmd = BuildIntCommand(storedProcName, parameters);
            objCmd.ExecuteNonQuery();
            intResult = (int)objCmd.Parameters["ReturnValue"].Value;
            objConn.Close();
            return intResult;
        }

        public int GetStatus(string storedProcName, IDataParameter[] parameters)
        {
            int intResult = 0;
            if (objConn.State == System.Data.ConnectionState.Closed)
            {
                objConn.Open();
            }

            //objCon.Open();
            SqlCommand objCmd = BuildQueryCommand(storedProcName, parameters);
            objCmd.ExecuteNonQuery();
            intResult = (int)objCmd.Parameters["intStatusID"].Value;
            objConn.Close();
            return intResult;
        }

        public DataSet GetDataSet(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            DataSet dataSet = new DataSet();
            try
            {
                if (objConn.State == System.Data.ConnectionState.Closed)
                {
                    objConn.Open();
                }
                SqlDataAdapter SqlDA = new SqlDataAdapter();
                SqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
                SqlDA.Fill(dataSet, tableName);
                objConn.Close();
            }
            catch (Exception ex)
            {
                string s = ex.Message.ToString();
                string a = s;
            }
            return dataSet;
        }

        public DataSet GetDataSet(string vstrQuery, string vstrTableName)
        {
            DataSet objDS = new DataSet();
            if (objConn.State == System.Data.ConnectionState.Closed)
            {
                objConn.Open();
            }
            SqlDataAdapter SqlDA = new SqlDataAdapter(vstrQuery, objConn);
            SqlDA.Fill(objDS, vstrTableName);
            objConn.Close();

            return objDS;
        }

        public SqlDataReader GetDataReader(string storedProcName, IDataParameter[] parameters)
        {
            try
            {
                SqlDataReader returnReader;
                if (objConn.State == System.Data.ConnectionState.Closed)
                {
                    objConn.Open();
                }
                SqlCommand command = BuildQueryCommand(storedProcName, parameters);
                command.CommandType = CommandType.StoredProcedure;
                returnReader = command.ExecuteReader();

                return returnReader;
            }
            catch (Exception ex)
            {
                string s = ex.Message.ToString();
                return (SqlDataReader)null;
            }
        }

        public SqlDataReader GetDataReader(string vstrQuery)
        {
            try
            {
                SqlDataReader returnReader;
                if (objConn.State == System.Data.ConnectionState.Closed)
                {
                    objConn.Open();
                }
                SqlCommand command = new SqlCommand(vstrQuery, objConn);
                returnReader = command.ExecuteReader();

                return returnReader;
            }
            catch (Exception ex)
            {
                string s = ex.ToString();
                return (SqlDataReader)null;
            }
        }

        public DataTable GetDataTable(string storedProcName, IDataParameter[] parameters, string strTableName)
        {
            DataTable objDT = new DataTable(strTableName);
            if (objConn.State == System.Data.ConnectionState.Closed)
            {
                objConn.Open();
            }
            SqlDataAdapter SqlDA = new SqlDataAdapter();
            SqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
            SqlDA.Fill(objDT);
            objConn.Close();

            return objDT;
        }


        protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            try
            {
                SqlDataReader returnReader;

                if (objConn.State == System.Data.ConnectionState.Closed)
                {
                    objConn.Open();
                }
                //objCon.Open();
                SqlCommand command = BuildQueryCommand(storedProcName, parameters);
                command.CommandType = CommandType.StoredProcedure;

                returnReader = command.ExecuteReader();

                return returnReader;
            }
            catch (Exception ex)
            {
                string s = ex.ToString();
                return (SqlDataReader)null;
            }
        }


        public DataTable GetDataTable(string storedProcName, string strTableName)
        {
            DataTable objDT = new DataTable(strTableName);
            if (objConn.State == System.Data.ConnectionState.Closed)
            {
                objConn.Open();
            }
            SqlDataAdapter SqlDA = new SqlDataAdapter();
            SqlDA.SelectCommand = BuildQueryCommand(storedProcName);
            SqlDA.Fill(objDT);
            objConn.Close();

            return objDT;
        }
    }
}